use ClassicDb
go

create database ClassicDb

GO



create table StudentInfo

(

    Id int PRIMARY key not null IDENTITY,

    StudentCode nvarchar(80),

    StudentName nvarchar(80),

    Birthday date not null,

    Sex nvarchar(2),

    ClassId int not null

)



GO



-- select * from StudentInfo



insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('01' , '赵雷' , '1990-01-01' , 'm',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('02' , '钱电' , '1990-12-21' , 'm',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('03' , '孙风' , '1990-12-20' , 'm',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('04' , '李云' , '1990-12-06' , 'm',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('05' , '周梅' , '1991-12-01' , 'f',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('06' , '吴兰' , '1992-01-01' , 'f',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('07' , '郑竹' , '1989-01-01' , 'f',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('09' , '张三' , '2017-12-20' , 'f',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('10' , '李四' , '2017-12-25' , 'f',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('11' , '李四' , '2012-06-06' , 'f',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('12' , '赵六' , '2013-06-13' , 'f',1)

insert into StudentInfo (StudentCode,StudentName,Birthday,Sex,ClassId) values('13' , '孙七' , '2014-06-01' , 'f',1)





GO





CREATE TABLE Teachers

(

    Id int PRIMARY key not null IDENTITY,

    TeacherName nvarchar(80)

)



go

-- select * from Teachers



insert into Teachers (TeacherName) values('张三')

insert into Teachers (TeacherName) values('李四')

insert into Teachers (TeacherName) values('王五')



GO



create table CourseInfo

(

    Id int PRIMARY key not null IDENTITY,

    CourseName NVARCHAR(80) not null,

    TeacherId int not null

)



go

-- select * from CourseInfo



insert into CourseInfo (CourseName,TeacherId) values( '语文' , 2)

insert into CourseInfo (CourseName,TeacherId) values( '数学' , 1)

insert into CourseInfo (CourseName,TeacherId) values( '英语' , 3)



GO



create table StudentCourseScore

(

    Id int PRIMARY key not null IDENTITY,

    StudentId int not null,

    CourseId int not null,

    Score int not null

)

go

-- select * from StudentCourseScore



insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='01') , 1 , 80)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='01') , 2 , 90)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='01') , 3 , 99)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='02') , 1 , 70)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='02') , 2 , 60)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='02') , 3 , 80)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='03') , 1 , 80)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='03') , 2 , 80)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='03') , 3 , 80)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='04') , 1 , 50)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='04') , 2 , 30)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='04') , 3 , 20)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='05') , 1 , 76)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='05') , 2 , 87)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='06') , 1 , 31)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='06') , 3 , 34)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='07') , 2 , 89)

insert into StudentCourseScore (StudentId,CourseId,Score) values((select Id from StudentInfo where StudentCode='07') , 3 , 98)



select * from StudentInfo
select * from Teachers
select * from CourseInfo
select * from StudentCourseScore
-- 1.查询"数学 "课程比" 语文 "课程成绩高的学生的信息及课程分数

select D.* from 
(select A.StudentId a,A.CourseId b,A.Score c,B.StudentId d,B.CourseId e,B.Score f from
(select * from StudentCourseScore where CourseId = 1) as A ,
(select * from StudentCourseScore where CourseId = 2) as B
where A.Score > B.Score and A.StudentId = B.StudentId) as C
inner join StudentInfo D on C.a = D.StudentCode
 

-- 1.1 查询同时存在" 数学 "课程和" 语文 "课程的情况

select StudentId,count(CourseId) from StudentCourseScore 
where CourseId = 1 or CourseId = 2
group by StudentId
having count(CourseId) = 2

-- 1.2 查询存在" 数学 "课程但可能不存在" 语文 "课程的情况(不存在时显示为 null )

select * from (select * from StudentCourseScore where CourseId = 1) as A
right join (select * from StudentCourseScore where CourseId = 2) as B on A.StudentId = B.StudentId 

-- 1.3 查询不存在" 数学 "课程但存在" 语文 "课程的情况

select * from (select * from StudentCourseScore where CourseId = 1) as A
left join (select * from StudentCourseScore where CourseId = 2) as B on A.StudentId = B.StudentId 

-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select StudentId as 学生编号,StudentName as 学生姓名,avg(Score) as 平均成绩 from StudentCourseScore A
inner join StudentInfo B on A.StudentId = B.StudentCode 
group by StudentId,StudentName
having avg(Score) >= 60

-- 3.查询在 成绩表 存在成绩的学生信息

select B.* from StudentCourseScore A
left join StudentInfo B on A.StudentId = B.StudentCode


-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select 
StudentCode as 学生编号,StudentName as 学生姓名,
count(CourseId) as 选课总数,sum(Score) as 总成绩 
from StudentCourseScore A
right join StudentInfo B on A.StudentId = B.StudentCode
group by StudentCode,StudentName
order by StudentCode

-- 4.1 查有成绩的学生信息

select StudentCode,StudentName,Birthday,Sex,ClassId from StudentCourseScore A
left join StudentInfo B on A.StudentId = B.StudentCode
group by StudentCode,StudentName,Birthday,Sex,ClassId

-- 5.查询「李」姓老师的数量

select count(*) as 姓老师的数量 from Teachers
group by TeacherName
having TeacherName like ('李%')


-- 6.查询学过「张三」老师授课的同学的信息

select B.* from StudentCourseScore A
inner join StudentInfo B on A.StudentId = B.StudentCode
inner join CourseInfo C on A.CourseId = C.Id
where A.CourseId = 1

-- 7.查询没有学全所有课程的同学的信息

select StudentId,StudentName,Birthday,Sex,ClassId from StudentCourseScore A 
inner join StudentInfo B on A.StudentId = B.StudentCode
group by StudentId,StudentName,Birthday,Sex,ClassId
having count(CourseId)<3 

-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

select StudentId,StudentName,Birthday,Sex,ClassId from StudentCourseScore A
inner join StudentInfo B on A.StudentId = B.StudentCode
where CourseId = 1 or CourseId = 2 or CourseId = 3
group by StudentId,StudentName,Birthday,Sex,ClassId

-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

select StudentCode,StudentName,Birthday,Sex,ClassId from StudentCourseScore A
inner join StudentInfo B on A.StudentId = B.StudentCode
where CourseId = 1 or CourseId = 2 or CourseId = 3
group by StudentCode,StudentName,Birthday,Sex,ClassId
having count(CourseId) = 3

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名

select StudentName from StudentCourseScore A
inner join StudentInfo B on A.StudentId = B.StudentCode
inner join CourseInfo C on A.CourseId = C.Id
where A.CourseId != 1
group by StudentCode,StudentName